{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 封闭基金净增"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import xcsc_tushare as xc \n",
    "import datetime\n",
    "import re\n",
    "import sys\n",
    "sys.path.append('..')\n",
    "from configure.settings import config\n",
    "\n",
    "xc_server=config['xc_server']\n",
    "xc_token_pro=config['xc_token_pro']\n",
    "\n",
    "\n",
    "xc.set_token(xc_token_pro)\n",
    "pro = xc.pro_api(env='prd',server=xc_server)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pro.fund_basic(market='E',status='L')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>name</th>\n",
       "      <th>management</th>\n",
       "      <th>custodian</th>\n",
       "      <th>fund_type</th>\n",
       "      <th>found_date</th>\n",
       "      <th>due_date</th>\n",
       "      <th>list_date</th>\n",
       "      <th>issue_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>...</th>\n",
       "      <th>exp_return</th>\n",
       "      <th>benchmark</th>\n",
       "      <th>status</th>\n",
       "      <th>invest_type</th>\n",
       "      <th>type</th>\n",
       "      <th>trustee</th>\n",
       "      <th>purc_startdate</th>\n",
       "      <th>redm_startdate</th>\n",
       "      <th>market</th>\n",
       "      <th>update_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>159611.SZ</td>\n",
       "      <td>电力ETF</td>\n",
       "      <td>广发基金</td>\n",
       "      <td>中国建设银行</td>\n",
       "      <td>股票型</td>\n",
       "      <td>20211229</td>\n",
       "      <td>None</td>\n",
       "      <td>20220107</td>\n",
       "      <td>20211220</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>中证全指电力公用事业指数收益率</td>\n",
       "      <td>L</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>None</td>\n",
       "      <td>20220107</td>\n",
       "      <td>20220107</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>560800.SH</td>\n",
       "      <td>数字经济ETF</td>\n",
       "      <td>鹏扬基金</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>股票型</td>\n",
       "      <td>20211222</td>\n",
       "      <td>None</td>\n",
       "      <td>20220107</td>\n",
       "      <td>20211206</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>中证数字经济主题指数收益率</td>\n",
       "      <td>L</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>None</td>\n",
       "      <td>20220107</td>\n",
       "      <td>20220107</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>562510.SH</td>\n",
       "      <td>旅游ETF</td>\n",
       "      <td>华夏基金</td>\n",
       "      <td>中国银行</td>\n",
       "      <td>股票型</td>\n",
       "      <td>20211221</td>\n",
       "      <td>None</td>\n",
       "      <td>20211230</td>\n",
       "      <td>20211116</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>中证旅游主题指数收益率</td>\n",
       "      <td>L</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>None</td>\n",
       "      <td>20211230</td>\n",
       "      <td>20211230</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>159758.SZ</td>\n",
       "      <td>红利50ETF</td>\n",
       "      <td>华夏基金</td>\n",
       "      <td>中信银行</td>\n",
       "      <td>股票型</td>\n",
       "      <td>20211220</td>\n",
       "      <td>None</td>\n",
       "      <td>20211228</td>\n",
       "      <td>20211111</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>中证红利质量指数收益率</td>\n",
       "      <td>L</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>None</td>\n",
       "      <td>20211228</td>\n",
       "      <td>20211228</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>562300.SH</td>\n",
       "      <td>碳中和ETF基金</td>\n",
       "      <td>银华基金</td>\n",
       "      <td>招商银行</td>\n",
       "      <td>股票型</td>\n",
       "      <td>20211220</td>\n",
       "      <td>None</td>\n",
       "      <td>20220105</td>\n",
       "      <td>20211110</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>中证内地低碳经济主题指数收益率</td>\n",
       "      <td>L</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>None</td>\n",
       "      <td>20220105</td>\n",
       "      <td>20220105</td>\n",
       "      <td>E</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code      name management custodian fund_type found_date due_date  \\\n",
       "0  159611.SZ     电力ETF       广发基金    中国建设银行       股票型   20211229     None   \n",
       "1  560800.SH   数字经济ETF       鹏扬基金      招商银行       股票型   20211222     None   \n",
       "2  562510.SH     旅游ETF       华夏基金      中国银行       股票型   20211221     None   \n",
       "3  159758.SZ   红利50ETF       华夏基金      中信银行       股票型   20211220     None   \n",
       "4  562300.SH  碳中和ETF基金       银华基金      招商银行       股票型   20211220     None   \n",
       "\n",
       "  list_date issue_date delist_date  ...  exp_return        benchmark  status  \\\n",
       "0  20220107   20211220        None  ...        None  中证全指电力公用事业指数收益率       L   \n",
       "1  20220107   20211206        None  ...        None    中证数字经济主题指数收益率       L   \n",
       "2  20211230   20211116        None  ...        None      中证旅游主题指数收益率       L   \n",
       "3  20211228   20211111        None  ...        None      中证红利质量指数收益率       L   \n",
       "4  20220105   20211110        None  ...        None  中证内地低碳经济主题指数收益率       L   \n",
       "\n",
       "   invest_type    type  trustee purc_startdate redm_startdate market  \\\n",
       "0        被动指数型  契约型开放式     None       20220107       20220107      E   \n",
       "1        被动指数型  契约型开放式     None       20220107       20220107      E   \n",
       "2        被动指数型  契约型开放式     None       20211230       20211230      E   \n",
       "3        被动指数型  契约型开放式     None       20211228       20211228      E   \n",
       "4        被动指数型  契约型开放式     None       20220105       20220105      E   \n",
       "\n",
       "  update_flag  \n",
       "0           1  \n",
       "1           1  \n",
       "2           1  \n",
       "3           1  \n",
       "4           1  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1081 entries, 0 to 1080\n",
      "Data columns (total 26 columns):\n",
      " #   Column          Non-Null Count  Dtype  \n",
      "---  ------          --------------  -----  \n",
      " 0   ts_code         1081 non-null   object \n",
      " 1   name            1081 non-null   object \n",
      " 2   management      1081 non-null   object \n",
      " 3   custodian       1081 non-null   object \n",
      " 4   fund_type       1081 non-null   object \n",
      " 5   found_date      1081 non-null   object \n",
      " 6   due_date        8 non-null      object \n",
      " 7   list_date       1081 non-null   object \n",
      " 8   issue_date      1074 non-null   object \n",
      " 9   delist_date     0 non-null      object \n",
      " 10  issue_amount    1066 non-null   float64\n",
      " 11  m_fee           1081 non-null   float64\n",
      " 12  c_fee           1081 non-null   float64\n",
      " 13  duration_year   11 non-null     float64\n",
      " 14  p_value         1081 non-null   float64\n",
      " 15  min_amount      1021 non-null   float64\n",
      " 16  exp_return      0 non-null      object \n",
      " 17  benchmark       1070 non-null   object \n",
      " 18  status          1081 non-null   object \n",
      " 19  invest_type     1070 non-null   object \n",
      " 20  type            1081 non-null   object \n",
      " 21  trustee         0 non-null      object \n",
      " 22  purc_startdate  1008 non-null   object \n",
      " 23  redm_startdate  1008 non-null   object \n",
      " 24  market          1081 non-null   object \n",
      " 25  update_flag     1081 non-null   object \n",
      "dtypes: float64(6), object(20)\n",
      "memory usage: 219.7+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "f_df = pro.fund_daily(ts_code='160527.SZ', start_date='20200101', end_date='20220105')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>trade_date</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20220105</td>\n",
       "      <td>1.091</td>\n",
       "      <td>1.086</td>\n",
       "      <td>1.090</td>\n",
       "      <td>1.076</td>\n",
       "      <td>1.081</td>\n",
       "      <td>-0.010</td>\n",
       "      <td>-0.9166</td>\n",
       "      <td>1401.57</td>\n",
       "      <td>151.630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20220104</td>\n",
       "      <td>1.093</td>\n",
       "      <td>1.087</td>\n",
       "      <td>1.092</td>\n",
       "      <td>1.084</td>\n",
       "      <td>1.091</td>\n",
       "      <td>-0.002</td>\n",
       "      <td>-0.1830</td>\n",
       "      <td>1050.59</td>\n",
       "      <td>114.123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20211231</td>\n",
       "      <td>1.091</td>\n",
       "      <td>1.088</td>\n",
       "      <td>1.093</td>\n",
       "      <td>1.088</td>\n",
       "      <td>1.093</td>\n",
       "      <td>0.002</td>\n",
       "      <td>0.1833</td>\n",
       "      <td>1315.00</td>\n",
       "      <td>143.221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20211230</td>\n",
       "      <td>1.088</td>\n",
       "      <td>1.092</td>\n",
       "      <td>1.093</td>\n",
       "      <td>1.091</td>\n",
       "      <td>1.091</td>\n",
       "      <td>0.003</td>\n",
       "      <td>0.2757</td>\n",
       "      <td>1453.00</td>\n",
       "      <td>158.654</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20211229</td>\n",
       "      <td>1.097</td>\n",
       "      <td>1.094</td>\n",
       "      <td>1.094</td>\n",
       "      <td>1.084</td>\n",
       "      <td>1.088</td>\n",
       "      <td>-0.009</td>\n",
       "      <td>-0.8204</td>\n",
       "      <td>924.00</td>\n",
       "      <td>100.847</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code trade_date  pre_close   open   high    low  close  change  \\\n",
       "0  160527.SZ   20220105      1.091  1.086  1.090  1.076  1.081  -0.010   \n",
       "1  160527.SZ   20220104      1.093  1.087  1.092  1.084  1.091  -0.002   \n",
       "2  160527.SZ   20211231      1.091  1.088  1.093  1.088  1.093   0.002   \n",
       "3  160527.SZ   20211230      1.088  1.092  1.093  1.091  1.091   0.003   \n",
       "4  160527.SZ   20211229      1.097  1.094  1.094  1.084  1.088  -0.009   \n",
       "\n",
       "   pct_chg      vol   amount  \n",
       "0  -0.9166  1401.57  151.630  \n",
       "1  -0.1830  1050.59  114.123  \n",
       "2   0.1833  1315.00  143.221  \n",
       "3   0.2757  1453.00  158.654  \n",
       "4  -0.8204   924.00  100.847  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_netvalue = pro.fund_nav(ts_code='160527.SZ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>ann_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>unit_nav</th>\n",
       "      <th>accum_nav</th>\n",
       "      <th>accum_div</th>\n",
       "      <th>net_asset</th>\n",
       "      <th>total_netasset</th>\n",
       "      <th>adj_nav</th>\n",
       "      <th>update_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20220106</td>\n",
       "      <td>20220105</td>\n",
       "      <td>1.1305</td>\n",
       "      <td>1.1635</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>1.162226</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  ann_date  end_date  unit_nav  accum_nav accum_div net_asset  \\\n",
       "0  160527.SZ  20220106  20220105    1.1305     1.1635      None      None   \n",
       "\n",
       "  total_netasset   adj_nav update_flag  \n",
       "0           None  1.162226           0  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_netvalue.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pro.fund_nav(ts_code='160527.SZ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>ann_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>unit_nav</th>\n",
       "      <th>accum_nav</th>\n",
       "      <th>accum_div</th>\n",
       "      <th>net_asset</th>\n",
       "      <th>total_netasset</th>\n",
       "      <th>adj_nav</th>\n",
       "      <th>update_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200418</td>\n",
       "      <td>20200417</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200411</td>\n",
       "      <td>20200410</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200404</td>\n",
       "      <td>20200403</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200328</td>\n",
       "      <td>20200327</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200321</td>\n",
       "      <td>20200320</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>None</td>\n",
       "      <td>2.053794e+09</td>\n",
       "      <td>2.126906e+09</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ts_code  ann_date  end_date  unit_nav  accum_nav accum_div  \\\n",
       "396  160527.SZ  20200418  20200417    1.0146     1.0146      None   \n",
       "397  160527.SZ  20200411  20200410    1.0072     1.0072      None   \n",
       "398  160527.SZ  20200404  20200403    1.0015     1.0015      None   \n",
       "399  160527.SZ  20200328  20200327    0.9996     0.9996      None   \n",
       "400  160527.SZ  20200321  20200320    1.0000     1.0000      None   \n",
       "\n",
       "        net_asset  total_netasset  adj_nav update_flag  \n",
       "396           NaN             NaN   1.0146           1  \n",
       "397           NaN             NaN   1.0072           1  \n",
       "398           NaN             NaN   1.0015           1  \n",
       "399           NaN             NaN   0.9996           1  \n",
       "400  2.053794e+09    2.126906e+09   1.0000           1  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>ann_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>unit_nav</th>\n",
       "      <th>accum_nav</th>\n",
       "      <th>accum_div</th>\n",
       "      <th>net_asset</th>\n",
       "      <th>total_netasset</th>\n",
       "      <th>adj_nav</th>\n",
       "      <th>update_flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>396</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200418</td>\n",
       "      <td>20200417</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>397</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200411</td>\n",
       "      <td>20200410</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0072</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>398</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200404</td>\n",
       "      <td>20200403</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0015</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>399</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200328</td>\n",
       "      <td>20200327</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.9996</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>400</th>\n",
       "      <td>160527.SZ</td>\n",
       "      <td>20200321</td>\n",
       "      <td>20200320</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>None</td>\n",
       "      <td>2.053794e+09</td>\n",
       "      <td>2.126906e+09</td>\n",
       "      <td>1.0000</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ts_code  ann_date  end_date  unit_nav  accum_nav accum_div  \\\n",
       "396  160527.SZ  20200418  20200417    1.0146     1.0146      None   \n",
       "397  160527.SZ  20200411  20200410    1.0072     1.0072      None   \n",
       "398  160527.SZ  20200404  20200403    1.0015     1.0015      None   \n",
       "399  160527.SZ  20200328  20200327    0.9996     0.9996      None   \n",
       "400  160527.SZ  20200321  20200320    1.0000     1.0000      None   \n",
       "\n",
       "        net_asset  total_netasset  adj_nav update_flag  \n",
       "396           NaN             NaN   1.0146           1  \n",
       "397           NaN             NaN   1.0072           1  \n",
       "398           NaN             NaN   1.0015           1  \n",
       "399           NaN             NaN   0.9996           1  \n",
       "400  2.053794e+09    2.126906e+09   1.0000           1  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_netvalue.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "sys.path.append('..')\n",
    "from configure.settings import DBSelector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = DBSelector().get_engine('db_closed_end_daily')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_file = '/home/xda/hub/stock/data/closed_end_data.xlsx'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel(excel_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>price</th>\n",
       "      <th>percent</th>\n",
       "      <th>netvalue_increment</th>\n",
       "      <th>daily_diff</th>\n",
       "      <th>vol</th>\n",
       "      <th>netvalue</th>\n",
       "      <th>tradedate</th>\n",
       "      <th>evaluate</th>\n",
       "      <th>preiumrate</th>\n",
       "      <th>end_date</th>\n",
       "      <th>remain_year</th>\n",
       "      <th>remain_year_profit</th>\n",
       "      <th>year_perium_ratio</th>\n",
       "      <th>week_netvalue_increment</th>\n",
       "      <th>week_diff</th>\n",
       "      <th>stock_percent</th>\n",
       "      <th>report_date</th>\n",
       "      <th>company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>160143</td>\n",
       "      <td>创业LOF</td>\n",
       "      <td>1.143</td>\n",
       "      <td>-0.0112</td>\n",
       "      <td>-0.0051</td>\n",
       "      <td>0.0061</td>\n",
       "      <td>135.36</td>\n",
       "      <td>1.1917</td>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>1.1917</td>\n",
       "      <td>0.04087</td>\n",
       "      <td>2022-09-01</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.06297</td>\n",
       "      <td>-0.0756</td>\n",
       "      <td>-0.0554</td>\n",
       "      <td>-0.0202</td>\n",
       "      <td>0.9748</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>南方基金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>160325</td>\n",
       "      <td>华夏创业</td>\n",
       "      <td>1.127</td>\n",
       "      <td>-0.0088</td>\n",
       "      <td>-0.0037</td>\n",
       "      <td>0.0051</td>\n",
       "      <td>384.11</td>\n",
       "      <td>1.1818</td>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>1.1818</td>\n",
       "      <td>0.04637</td>\n",
       "      <td>2022-07-24</td>\n",
       "      <td>0.54</td>\n",
       "      <td>0.08555</td>\n",
       "      <td>-0.0795</td>\n",
       "      <td>-0.0701</td>\n",
       "      <td>-0.0093</td>\n",
       "      <td>0.9091</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>华夏基金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>160526</td>\n",
       "      <td>博时优势</td>\n",
       "      <td>1.271</td>\n",
       "      <td>-0.0047</td>\n",
       "      <td>-0.0005</td>\n",
       "      <td>0.0042</td>\n",
       "      <td>3.29</td>\n",
       "      <td>1.3200</td>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>1.3200</td>\n",
       "      <td>0.03712</td>\n",
       "      <td>2022-06-03</td>\n",
       "      <td>0.40</td>\n",
       "      <td>0.09211</td>\n",
       "      <td>-0.0165</td>\n",
       "      <td>-0.0223</td>\n",
       "      <td>0.0058</td>\n",
       "      <td>0.8125</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>博时基金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>160527</td>\n",
       "      <td>研究优选</td>\n",
       "      <td>1.075</td>\n",
       "      <td>-0.0028</td>\n",
       "      <td>0.0005</td>\n",
       "      <td>0.0033</td>\n",
       "      <td>8.62</td>\n",
       "      <td>1.1335</td>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>1.1335</td>\n",
       "      <td>0.05161</td>\n",
       "      <td>2023-03-20</td>\n",
       "      <td>1.20</td>\n",
       "      <td>0.04312</td>\n",
       "      <td>-0.0264</td>\n",
       "      <td>-0.0165</td>\n",
       "      <td>-0.0099</td>\n",
       "      <td>0.9649</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>博时基金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>160529</td>\n",
       "      <td>创业博时</td>\n",
       "      <td>1.198</td>\n",
       "      <td>-0.0058</td>\n",
       "      <td>-0.0008</td>\n",
       "      <td>0.0050</td>\n",
       "      <td>91.37</td>\n",
       "      <td>1.2625</td>\n",
       "      <td>2022-01-07</td>\n",
       "      <td>1.2625</td>\n",
       "      <td>0.05109</td>\n",
       "      <td>2022-09-03</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.07800</td>\n",
       "      <td>-0.0564</td>\n",
       "      <td>-0.0485</td>\n",
       "      <td>-0.0080</td>\n",
       "      <td>0.8765</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>博时基金</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     code   name  price  percent  netvalue_increment  daily_diff     vol  \\\n",
       "0  160143  创业LOF  1.143  -0.0112             -0.0051      0.0061  135.36   \n",
       "1  160325   华夏创业  1.127  -0.0088             -0.0037      0.0051  384.11   \n",
       "2  160526   博时优势  1.271  -0.0047             -0.0005      0.0042    3.29   \n",
       "3  160527   研究优选  1.075  -0.0028              0.0005      0.0033    8.62   \n",
       "4  160529   创业博时  1.198  -0.0058             -0.0008      0.0050   91.37   \n",
       "\n",
       "   netvalue  tradedate  evaluate  preiumrate   end_date  remain_year  \\\n",
       "0    1.1917 2022-01-07    1.1917     0.04087 2022-09-01         0.65   \n",
       "1    1.1818 2022-01-07    1.1818     0.04637 2022-07-24         0.54   \n",
       "2    1.3200 2022-01-07    1.3200     0.03712 2022-06-03         0.40   \n",
       "3    1.1335 2022-01-07    1.1335     0.05161 2023-03-20         1.20   \n",
       "4    1.2625 2022-01-07    1.2625     0.05109 2022-09-03         0.66   \n",
       "\n",
       "   remain_year_profit  year_perium_ratio  week_netvalue_increment  week_diff  \\\n",
       "0             0.06297            -0.0756                  -0.0554    -0.0202   \n",
       "1             0.08555            -0.0795                  -0.0701    -0.0093   \n",
       "2             0.09211            -0.0165                  -0.0223     0.0058   \n",
       "3             0.04312            -0.0264                  -0.0165    -0.0099   \n",
       "4             0.07800            -0.0564                  -0.0485    -0.0080   \n",
       "\n",
       "   stock_percent report_date company  \n",
       "0         0.9748  2021-09-30    南方基金  \n",
       "1         0.9091  2021-09-30    华夏基金  \n",
       "2         0.8125  2021-09-30    博时基金  \n",
       "3         0.9649  2021-09-30    博时基金  \n",
       "4         0.8765  2021-09-30    博时基金  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['code']=df['code'].map(lambda x:str(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "codes = df['code'].values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "for code in codes:\n",
    "    for _ in range(5):\n",
    "        if code.startswith('1'):\n",
    "            code=code+'.SZ'\n",
    "        else:\n",
    "            code=code+'.SH'\n",
    "        try:\n",
    "            fund_netvalue  = pro.fund_nav(ts_code=code)\n",
    "            fund_netvalue.to_sql(code,con=engine,if_exists='replace')\n",
    "        except Exception as e:\n",
    "            print(e)\n",
    "        else:\n",
    "            break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "9aa71a1095d6a6dd446f2b486146a60837bd82849c2fc6b4bf786ffe99bd2ba1"
  },
  "kernelspec": {
   "display_name": "Python 3.9.5 64-bit ('3.9': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
